﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using AlexandriaPressAdminObject;

namespace AlexandriaPressAdminDataAccessLayer
{
    public class ProofReaderDL
    {
        private static string myConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["BookConn1"].ConnectionString;
            }
        }

        public static int checkEmail(string email)
        { 
            int check = 0;

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            if (conn.State != ConnectionState.Open) conn.Open();
            try
            {
                SqlCommand checkMail = new SqlCommand();
                checkMail.Connection = conn;
                checkMail.CommandType = CommandType.StoredProcedure;
                checkMail.CommandText = "usp_user_uniqueEmail";

                SqlParameter param1 = new SqlParameter();
                param1.DbType = DbType.String;
                param1.Value = email;
                param1.ParameterName = "@email";
                param1.Direction = ParameterDirection.Input;
                checkMail.Parameters.Add(param1);

                check = Convert.ToInt32(checkMail.ExecuteScalar());
            }
            catch
            {
                check = 0;
            }
            finally
            {
                conn.Close();
            }
            return check; ;
        
        
        }


        public static int updateProof(string login)
        {
            int check = 0;

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            if (conn.State != ConnectionState.Open) conn.Open();
            try
            {
                SqlCommand checkMail = new SqlCommand();
                checkMail.Connection = conn;
                checkMail.CommandType = CommandType.StoredProcedure;
                checkMail.CommandText = "usp_Change_To_ProofReader";

                SqlParameter param1 = new SqlParameter();
                param1.DbType = DbType.String;
                param1.Value = login;
                param1.ParameterName = "@login";
                param1.Direction = ParameterDirection.Input;
                checkMail.Parameters.Add(param1);

                check = Convert.ToInt32(checkMail.ExecuteNonQuery());
            }

            catch
            {
                check = 0;
            }
            finally
            {
                conn.Close();
            }
            return check; ;


        }



        public static List<ProofReaderInfo> displayAll()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_View_Proofreader_List";

            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<ProofReaderInfo> lstOfProofreader = new List<ProofReaderInfo>();
            ProofReaderInfo proofReader = null;

            while (dtReader.Read())
            {
                proofReader = new ProofReaderInfo();
                proofReader.ID = dtReader.GetInt32(dtReader.GetOrdinal("UserID"));
                proofReader.ProfilePicture = "~/MasterPages/UserPages/"+dtReader.GetString(dtReader.GetOrdinal("ProfilePicture"));
                proofReader.Name = dtReader.GetString(dtReader.GetOrdinal("FirstName")) + " " + dtReader.GetString(dtReader.GetOrdinal("LastName"));
                proofReader.DateJoined = dtReader.GetDateTime(dtReader.GetOrdinal("DateJoined"));
                lstOfProofreader.Add(proofReader);
            }
            return lstOfProofreader;
        }

        public static int removeProofreader(List<ProofReaderInfo> _proofreaderList)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            if (conn.State != ConnectionState.Open) conn.Open();

            int a=0;
            SqlTransaction transaction = conn.BeginTransaction();
            try
            {

                foreach (ProofReaderInfo proofreader in _proofreaderList)
                {
                    SqlCommand removeProofreader = new SqlCommand();
                    removeProofreader.Connection = conn;
                    removeProofreader.CommandType = CommandType.StoredProcedure;
                    removeProofreader.CommandText = "usp_Remove_Proofreader";
                    removeProofreader.Transaction = transaction;

                    SqlParameter param1 = new SqlParameter();
                    param1.DbType = DbType.Int32;
                    param1.Value = proofreader.ID;
                    param1.ParameterName = "@userId";
                    param1.Direction = ParameterDirection.Input;
                    removeProofreader.Parameters.Add(param1);
 
                    a = Convert.ToInt32(removeProofreader.ExecuteScalar());

                    transaction.Commit();

                }
            }
            catch 
            {
                transaction.Rollback();
                a = 0;
            }
            finally
            {
            conn.Close();
                transaction.Dispose();
            }
            return a;
        }

        public static bool createLogin(ProofReaderInfo _proofInfo)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlTransaction transaction = conn.BeginTransaction();
            try
            {
                SqlCommand addProofreader = new SqlCommand();
                addProofreader.Connection = conn;
                addProofreader.CommandType = CommandType.StoredProcedure;
                addProofreader.CommandText = "usp_Generate_Proofreader_Login";
                addProofreader.Transaction = transaction;
    
                SqlParameter param1 = new SqlParameter();
                param1.DbType = DbType.String;
                param1.Value = _proofInfo.FirstName;
                param1.ParameterName = "@firstName";
                param1.Direction = ParameterDirection.Input;
                addProofreader.Parameters.Add(param1);

                SqlParameter param2 = new SqlParameter();
                param2.DbType = DbType.String;
                param2.Value = _proofInfo.LastName;
                param2.ParameterName = "@lastName";
                param2.Direction = ParameterDirection.Input;
                addProofreader.Parameters.Add(param2);

                SqlParameter param3 = new SqlParameter();
                param3.DbType = DbType.String;
                param3.Value = _proofInfo.Email;
                param3.ParameterName = "@email";
                param3.Direction = ParameterDirection.Input;
                addProofreader.Parameters.Add(param3);

                SqlParameter param4 = new SqlParameter();
                param4.DbType = DbType.String;
                param4.Value = _proofInfo.Gender;
                param4.ParameterName = "@gender";
                param4.Direction = ParameterDirection.Input;
                addProofreader.Parameters.Add(param4);

                SqlParameter param5 = new SqlParameter();
                param5.DbType = DbType.DateTime;
                param5.Value = _proofInfo.Birthday;
                param5.ParameterName = "@birthday";
                param5.Direction = ParameterDirection.Input;
                addProofreader.Parameters.Add(param5);

                SqlParameter param6 = new SqlParameter();
                param6.DbType = DbType.String;
                param6.Value = _proofInfo.Password;
                param6.ParameterName = "@randomPassword";
                param6.Direction = ParameterDirection.Input;
                addProofreader.Parameters.Add(param6);

                addProofreader.ExecuteScalar();
                return true;
            }
            catch
            {
                transaction.Rollback();
                return false;
            }
            finally
            {
                conn.Close();
                transaction.Dispose();
            }

        }
    }
}
